﻿#region Creditos

/* Autor: Edilberto Sánchez Forero	*/
/* Email: esanchezfo@gmail.com		*/
/* Licencia: LGPL, Software Libre	*/
/* País: Colombia, Bogotá			*/

#endregion

#region Librerias

using System;
using System.Collections.Generic;
using System.Reflection;
//using System.Threading.Tasks;
using System.Diagnostics;
using System.Drawing;

using Microsoft.Office.Interop.Excel;

using ESF.General;
using ESF.MSExcel.Structures;
//using ESF.Bulk;
using System.IO;
using ESF.General.Text;

#endregion


namespace ESF.MSExcel
{
	public static class XlsReaderOld
	{
		#region Constantes
		
		private const int MAX_TRYCOUNT_READCELL = 5;
		private const int MAX_READTIME_MS = 1000;
		
		#endregion
	
	
		#region Lectura Basica
		
		public static void ReadUsedRangeFirstSheet (string pXLSFilename, Action<Range> pAction)
		{
			ReadUsedRange (pXLSFilename, 1, pAction);
		}


		internal static Microsoft.Office.Interop.Excel.Application GetExcelInstance ()
		{
			Application xlsApp = null;
			
		
			xlsApp = new Microsoft.Office.Interop.Excel.Application ();
				
			xlsApp.Visible = false;
			xlsApp.ScreenUpdating = false;
				
			xlsApp.EnableAnimations = false;
			xlsApp.EnableEvents = false;
			xlsApp.EnableLargeOperationAlert = false;
			xlsApp.EnableLivePreview = false;
			xlsApp.EnableSound = false;
			xlsApp.EnableTipWizard = false;
				
			xlsApp.DisplayClipboardWindow = false;
			xlsApp.DisplayAlerts = false;
			xlsApp.DisplayDocumentInformationPanel = false;
			xlsApp.DisplayExcel4Menus = false;

			return xlsApp;
		}


		public static string [] GetSheetNames (string pXLSFilename)
		{
			Application xlsApp = null;
			
			Workbook xlsWorkBook = null;
			
			List<string> sheetNames;
		
			
			System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
			System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");


			try
			{
				xlsApp = GetExcelInstance ();
				
				xlsWorkBook = xlsApp.Workbooks.Open (pXLSFilename, 0, true, 
					Missing.Value, string.Empty, string.Empty, false, Missing.Value, 
					Missing.Value, false, false, 0, false, 1, 0);
					
				sheetNames = new List<string> (xlsWorkBook.Worksheets.Count);

				foreach (Worksheet s in xlsWorkBook.Worksheets)
				{
					sheetNames.Add (s.Name);
				}
				
				xlsWorkBook.Close(false, pXLSFilename, Missing.Value);
				
				xlsApp.Quit();

				return sheetNames.ToArray ();
			}
			catch (Exception)
			{
				throw;
			}
			finally
			{
				System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
				
				ProcessHelper.ReleaseCOMObjects (xlsWorkBook, xlsApp);
			}
		}
		
		
		public static void ReadUsedRange (string pXLSFilename, int pSheetIndex, Action<Range> pAction)
		{	
			Application xlsApp = null;
			
			Workbook xlsWorkBook = null;
			
			Worksheet xlsWorkSheet = null;
		
			
			System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
			System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");


			try
			{
				xlsApp = GetExcelInstance ();
				
				xlsWorkBook = xlsApp.Workbooks.Open (pXLSFilename, 0, true, 
					Missing.Value, string.Empty, string.Empty, false, Missing.Value, 
					Missing.Value, false, false, 0, false, 1, 0);
					
				xlsWorkSheet = (Worksheet) xlsWorkBook.Worksheets.get_Item(pSheetIndex);

				pAction (xlsWorkSheet.UsedRange);	
				
				xlsWorkBook.Close(false, pXLSFilename, Missing.Value);
				
				xlsApp.Quit();
			}
			catch (Exception)
			{
				throw;
			}
			finally
			{
				System.Threading.Thread.CurrentThread.CurrentCulture = CurrentCI;
				
				ProcessHelper.ReleaseCOMObjects (xlsWorkSheet, xlsWorkBook, xlsApp);
			}
		}
		
	
		public static XlsCell GetCell (Range pRange)
		{
			object objContent;
			
			XlsCell cell;
			
			
			objContent = pRange.get_Value (Missing.Value);
			
			cell = new XlsCell (pRange.Row, pRange.Column);
			
			if (objContent != null)
			{
				cell.Content = objContent.ToString ();
			}
			
			if (pRange.Comment != null)
			{
				// Remueve el autor y titulo extra
				cell.Comment = pRange.Comment.Shape.AlternativeText;
				
				if (!string.IsNullOrEmpty (cell.Comment))
				{
					cell.Comment = cell.Comment.Substring (cell.Comment.IndexOf ('\n') + 1);
				}
			}
			
			cell.OutlineLevel = Convert.ToDouble (pRange.EntireRow.OutlineLevel);
			
			cell.Background = ColorTranslator.FromOle (Convert.ToInt32 (pRange.Interior.Color));
			
			
			return cell;
		}
		
		
		private static void ReadRowCells (XlsCell [,] pMtxCells, Range pUsedRange, int pRowIndex)
		{
			for (int colIndex = 0; colIndex < pMtxCells.GetUpperBound (1) + 1; colIndex ++)
			{
				pMtxCells [pRowIndex, colIndex] = GetCell ((Range) pUsedRange.get_Item (pRowIndex + 1, colIndex + 1));
			}
		}
		
		
		private static void ReadRow (XlsCell [,] pMtxCells, Range pUsedRange, int pRowStartIndex, int pRowEndIndex)
		{
			/*Parallel.For (pRowStartIndex, pRowEndIndex, (rowIndex, forState) =>
			{
				try
				{
					ReadRowCells (pMtxCells, pUsedRange, rowIndex);
				}
				catch (Exception)
				{
					forState.Stop ();
					throw;
				}
			});*/
		}
		
		
		public static XlsCell[,] GetAllCellsFirstSheetWithCache(string pXLSFilename)
		{
			return GetAllCells (pXLSFilename, 1, true);
		}
		
		
		public static XlsCell[,] GetAllCellsFirstSheet(string pXLSFilename, bool pUseCache)
		{
			return GetAllCells (pXLSFilename, 1, pUseCache);
		}


		public static XlsCell[,] GetByColumnCellsFirstSheet(string pXLSFilename, string pCellContentToFindMark, bool pUseCache)
		{
			int rowIndex, colIndex, foundColIndex = 0, foundRowIndex = 0;

			bool foundMark = false;

			XlsCell [,] data;

			XlsCell [,] dataResult;


			data = GetAllCellsFirstSheet (pXLSFilename, pUseCache); 

			for (rowIndex = 0; rowIndex < data.GetLength (0); rowIndex ++)
			{
				for (colIndex = 0; colIndex < data.GetLength (1); colIndex ++)
				{
					if (string.Compare (data [rowIndex, colIndex].Content, "NIT", true) == 0)
					{
						foundColIndex = colIndex;
						foundRowIndex = rowIndex;
						foundMark = true;
						break;
					}
				}
			}	

			if (!foundMark)
			{
				return null;
			}

			if (foundColIndex == 0 && foundRowIndex == 0)
			{
				return data;
			}

			dataResult = new XlsCell [data.GetLength (0) - foundRowIndex, data.GetLength (1) - foundColIndex];

			for (rowIndex = 0; rowIndex < dataResult.GetLength (0); rowIndex ++)
			{
				for (colIndex = 0; colIndex < dataResult.GetLength (1); colIndex ++)
				{
					dataResult [rowIndex, colIndex] = data [rowIndex + foundRowIndex, colIndex + foundColIndex];
				}
			}

			return dataResult;
		}
			
		
		
		public static XlsCell[,] GetAllCells(string pXLSFilename, int pSheetIndex)
		{
			return GetAllCells (pXLSFilename, pSheetIndex, false);
		}
		
		
		public static XlsCell[,] GetAllCells(string pXLSFilename, int pSheetIndex, bool pUseCache)
		{
			XlsCell [,] mtxCells;
			
			//TasksQueue bulkXlsRead;
			
			string xlsCacheFilename;
			
			Stopwatch stopWatch;
			
			
			stopWatch = new Stopwatch ();
			
			if (!File.Exists (pXLSFilename))
			{
				throw new GenericException (GenericException.CRI_FILE_DOESNOTEXIST, string.Format ("File not found {0}", pXLSFilename));
			}
			
			mtxCells = null;
			
			xlsCacheFilename = string.Format ("{0}_Cache_{1}.ser", 
				Path.Combine (Path.GetDirectoryName (pXLSFilename),
				Path.GetFileNameWithoutExtension (pXLSFilename)), 
				new FileInfo (pXLSFilename).LastWriteTime.ToString ("yyyyMMdd_HHmmss"));
			
			if (pUseCache)
			{
				FileHelper.DeleteFilesByExtensionExceptOne (
					Path.GetDirectoryName (pXLSFilename),
					Path.GetFileName (xlsCacheFilename));
			
				if (File.Exists (xlsCacheFilename))
				{
					stopWatch.Start ();
					
					Console.WriteLine ("Reading from cache file {0}...", xlsCacheFilename);
					
					try
					{
						mtxCells = SerializeHelper.DeSerializeFromFileBin <XlsCell [,]> (xlsCacheFilename);
				
						stopWatch.Stop ();
						
						Console.WriteLine ("Completed in {0}", TextHelper.ToString (ref stopWatch));
				
						return mtxCells;
					}
					catch (Exception)
					{
						stopWatch.Reset ();
						
						Console.Error.WriteLine ("Fail in deserialization from cache, current will be regenerated...");
					}
				}
			}
			
			Console.WriteLine (" - Getting used range...");
			
			ReadUsedRange (pXLSFilename, pSheetIndex, (usedRange) =>
			{	
				mtxCells = new XlsCell[usedRange.Rows.Count, usedRange.Columns.Count];
				
				//bulkXlsRead = new TasksQueue ("Excel reading", MAX_TRYCOUNT_READCELL);
				
				// Crea las tareas de lectura
				//Parallel.For (0, mtxCells.GetUpperBound (0) + 1, (rowIndex) =>
				for (int rowIndex = 0; rowIndex < mtxCells.GetUpperBound (0) + 1; rowIndex ++)
				{
					for (int colIndex = 0; colIndex < mtxCells.GetUpperBound (1) + 1; colIndex ++)
					//Parallel.For (0, mtxCells.GetUpperBound (1) + 1, (colIndex) =>
					{
						new XlsReadCellTask (ref mtxCells, ref usedRange, rowIndex, colIndex).Do ();
						//bulkXlsRead.Enqueue (new XlsReadCellTask (ref mtxCells, ref usedRange, rowIndex, colIndex));
					}//);
				}
				//);
				
				//bulkXlsRead.WaitToComplete ();
			});
			
			if (pUseCache)
			{
				stopWatch.Start ();
			
				Console.WriteLine (" - Creating CacheFile: {0}", xlsCacheFilename);
				
				SerializeHelper.SerializeToFileBin <XlsCell [,]> (mtxCells, xlsCacheFilename);
				
				Console.WriteLine (" - Completed cachefile in {0}", TextHelper.ToString (ref stopWatch));
			
				stopWatch.Stop ();
			}
			
			return mtxCells;
		}


		public static List<T> GetListFromXLSFile<T> (string pFilename, bool pFirstRowIsHeader) where T : new ()
		{
			MemberInfo memberInfo;

			PropertyInfo [] propertiesOfT;

			List<T> result;

			List<ColumnAttribute> columns;
			ColumnAttribute currentColumn;

			XlsCell [,] contents;


			memberInfo = typeof (T);

			result = new List<T> ();

			propertiesOfT = typeof (T).GetProperties ();
			
			columns = new List<ColumnAttribute> ();

			foreach (PropertyInfo property in propertiesOfT)
			{
			    currentColumn = Attribute.GetCustomAttribute (property, typeof (ColumnAttribute), false) as ColumnAttribute;
				
				currentColumn.SeteableProperty = property;

				if (currentColumn != null)
				{
					columns.Add (currentColumn);
				}
			}

			
			try
			{
				contents = GetAllCellsFirstSheet (pFilename, false);

				

				/*// Se omite la primer fila
				if (pFirstRowIsHeader)
				{
					pReader.ReadLine ();
				}

				while ((currentLine = pReader.ReadLine ()) != null)
				{
					item = new T ();

					currentLineParts = currentLine.Split (pSeparator);

					if (currentLineParts.Length < columns.Count)
					{
						throw new GenericInfoException (string.Format ("Hay menos columnas para leer que las requeridas: {0} vs {1}", currentLineParts.Length, columns.Count));
					}
					
					foreach (CSVColumnAttribute col in columns)
					{
						if (!string.IsNullOrEmpty (col.DateFormat))
						{
							col.SeteableProperty.SetValue (item, TextHelper.GetNullableDateTime (currentLineParts [col.ColumnOrder], col.DateFormat), null);
						}
						else if (col.IsBool)
						{
							col.SeteableProperty.SetValue (item, TextHelper.GetNullableBool (currentLineParts [col.ColumnOrder]), null);
						}
						else if (col.IsInt)
						{
							col.SeteableProperty.SetValue (item, TextHelper.GetNullableInt (currentLineParts [col.ColumnOrder]), null);
						}
						else
						{
							col.SeteableProperty.SetValue (item, currentLineParts [col.ColumnOrder], null);
						}
					}

					result.Add (item);
				}
				*/

				return null;
				//return result;
			}
			catch (Exception)
			{
				throw new GenericException (string.Format ("No fue posible leer el archivo {0} como Excel", pFilename));
			}
		}
		
		#endregion
	
		
		#region Lectura Especializada, Estructura Basica
		
		public static XlsBCRelations GetAllBCRelations (string pXLSFilename,
			bool pUseCache, string pTagName, params string [] pIgnoreHeaders)
		{
			return GetAllBCRelations (pXLSFilename, 1, pUseCache, pTagName, pIgnoreHeaders);
		}
		
		
		public static XlsBCRelations GetAllBCRelations (string pXLSFilename, int pSheetIndex, bool pUseCache,
			string pTagName, params string [] pIgnoreHeaders)
		{
			XlsCell [,] mtxCells;
			
			XlsBCRelations pRelations;
			
			int lastSourceIndex = 3, lastTargetIndex = 2;
			
			
			mtxCells = GetAllCells (pXLSFilename, pSheetIndex, pUseCache);
			
			#region Locate Tags
			
			/*Parallel.Invoke (() => 
			{
				while (lastSourceIndex + 1 <= mtxCells.GetUpperBound (0) && 
					mtxCells [lastSourceIndex + 1, 1].Content != pTagName)
				{
					lastSourceIndex ++;
				}
			},
			() => 
			{
				while (lastTargetIndex + 1 <= mtxCells.GetUpperBound (1) && 
					mtxCells [1,lastTargetIndex + 1].Content != pTagName)
				{
					lastTargetIndex ++;
				}
			});
			*/
			pRelations = new XlsBCRelations ();
			
			/*Parallel.Invoke (() =>
			{
				pRelations.SourceTags = new List<XlsBCItem> ();
				
				Parallel.For (lastTargetIndex + 2, mtxCells.GetUpperBound (1) + 1, (colIndex) =>
				{
					if (mtxCells [1, colIndex].HasText)
					{
						pRelations.SourceTags.Add (new XlsBCItem (mtxCells [0, colIndex], mtxCells [1, colIndex]));
					}
				});
				
				Console.WriteLine (" -- Source Tags found {0}", pRelations.SourceTags.Count);
			},
			() =>
			{
				pRelations.TargetTags = new List<XlsBCItem> ();
				
				Parallel.For (lastSourceIndex + 2, mtxCells.GetUpperBound (0) + 1, (rowIndex) =>
				{
					if (mtxCells [rowIndex, 1].HasText)
					{
						pRelations.TargetTags.Add (new XlsBCItem (mtxCells [rowIndex, 0], mtxCells [rowIndex, 1]));
					}
				});
				
				Console.WriteLine (" -- Target Tags found {0}", pRelations.TargetTags.Count);
			});
			*/
			#endregion
			
			#region Source and Targets
			
			// Se recorren las primeras dos filas apartir de la C1 donde:
			// Fila 1 tiene los IDs
			// Fila 2 tiene los Textos
			
			#region Sources
			/*
			Parallel.Invoke (() =>
			{ 
				XlsBCItem tmpItem;
				
				
				pRelations.Sources = new List<XlsBCItem> ();
				
				
				Console.Write (" -- Extracting Sources...");
				
				for (int rowIndex = 3; rowIndex <= lastSourceIndex; rowIndex++)
				{
					if (!TextHelper.IsINSet (mtxCells[rowIndex, 1].Content, pIgnoreHeaders))
					{
						tmpItem = new XlsBCItem (mtxCells[rowIndex, 0], mtxCells[rowIndex, 1]);
						
						#region Extraccion de Relaciones con Targets
						
						// Solo se consideran los elementos con contenido 
						for (int colIndex = 2; colIndex < lastTargetIndex; colIndex++)
						{
							if (mtxCells [rowIndex, colIndex].HasText)
							{
								tmpItem.Relations.Add (mtxCells [rowIndex, colIndex]);
							}
						}
						
						#endregion
						
						#region Extraccion de Relaciones con Tags
						
						// Si hay Tags en la siguientes columnas (al menos una 1 titulo tag, 1 tag => + 2)
						if (lastSourceIndex + 1 < mtxCells.GetUpperBound (1))
						{
							// Solo se consideran los elementos con contenido
							for (int colIndex = lastTargetIndex + 2; colIndex < mtxCells.GetUpperBound (1) - 1; colIndex ++)
							{
								if (mtxCells [rowIndex, colIndex] != null)
								{
									tmpItem.TagRelations.Add (mtxCells [rowIndex, colIndex]);
								}
							}
						}
						
						#endregion
						
						pRelations.Sources.Add (tmpItem);
					}
				}
			});*/
			
			Console.WriteLine (" found {0}", pRelations.Sources.Count);
			
			#endregion
						
			#region Targets
			
			/*Parallel.Invoke (() =>
			{
				XlsBCItem tmpItem;
				
				
				Console.Write (" -- Extracting Targets...");
				
				pRelations.Targets = new List<XlsBCItem> ();
				
				for (int colIndex = 2; colIndex <= lastTargetIndex; colIndex ++)
				{
					if (!TextHelper.IsINSet(mtxCells[1, colIndex].Content, pIgnoreHeaders))
					{
						tmpItem = new XlsBCItem (mtxCells [0, colIndex], mtxCells [1, colIndex]);
						
						#region Extraccion de Relaciones con Tags
						
						// Si hay Tags en la siguientes filas (al menos una 1 titulo tag, 1 tag => + 2)
						if (lastTargetIndex + 1 < mtxCells.GetUpperBound (0))
						{
							// Solo se consideran los elementos con contenido
							Parallel.For (lastTargetIndex + 2, mtxCells.GetUpperBound (1) - 1, (rowIndex) =>
							{
								if (mtxCells [rowIndex, colIndex] != null)
								{
									tmpItem.TagRelations.Add (mtxCells [rowIndex, colIndex]);
								}
							});
						}
						
						#endregion

						pRelations.Targets.Add(tmpItem);
					}
				}
			});*/
			
			Console.WriteLine (" found {0}", pRelations.Targets.Count);
			
			#endregion 
		
			#endregion 


			return pRelations;
		}
		
		
		#endregion
	}
}
